const express = require('express');
const cors = require('cors');
const fs = require('fs');
const path = require('path');
const { v4: uuidv4 } = require('uuid');

const { Pool } = require('pg'); // 引入 pg 的 Pool 类

// --- 数据库连接配置 ---
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'postgres',
  password: '123456',
  port: 5432,
});

// --- 测试数据库连接 ---
pool.query('SELECT NOW()', (err, res) => {
  if (err) {
    console.error('❌ 数据库连接失败:', err.stack);
  } else {
    console.log('✅ 数据库连接成功，当前时间:', res.rows[0].now);
  }
});

const app = express();
const PORT = 8081;

// --- 中间件 ---
// 启用 CORS，允许前端访问
app.use(cors({
  origin: 'http://localhost:8080', // 替换为你的前端地址
  credentials: true // 允许携带 cookies
}));
// 解析 JSON 请求体
app.use(express.json());
// 解析 URL 编码的请求体 (application/x-www-form-urlencoded)
app.use(express.urlencoded({ extended: false }));

// --- API 路由 ---
/**
 * GET /metadata/:tableName
 * 根据参数tableName通过数据库读取tableName对应库表的元数据信息
 */
app.get('/metadata/:tableName', async (req, res) => {
  const { tableName } = req.params;

  try {
    const result = await pool.query(
      'SELECT * FROM public.metadata where "table" = $1 ORDER BY sort',
      [tableName]
    );
    // 对rule和dict字符串转为JSON对象
    result.rows.forEach(field => {
      if(field.rule) {
        field.rule = JSON.parse(field.rule)
      }
      if(field.dict) {
        field.dict = JSON.parse(field.dict)
      }
    });

    // 成功响应
    res.json({
      code: 200,
      message: '成功',
      data: result.rows
    });
  } catch (error) {
    console.error('查询失败:', error);
    res.status(500).json({
      code: 500,
      message: '元数据获取失败',
      data: null
    });
  }
});

/**
 * POST /getData
 * 根据参数tableName通过数据库读取tableName对应库表的元数据信息
 */
app.post('/getData', async (req, res) => {
  const { tableName,query,page } = req.body; // express.json() 解析

  const pageNum = parseInt(page.pageNum);
  const pageSize = parseInt(page.pageSize);
  const offset = (pageNum - 1) * pageSize;
  
  try {
    // 查询分页数据
    let sqlWhereStr = ''
    let param = []
    let index = 1
    if(query) {
      //where条件拼接
      for (const key in query) {
        if (query.hasOwnProperty(key)) {
          const value = query[key];
          if(Array.isArray(value)) {
            if(value.length === 2) {
              param.push(...value)
              sqlWhereStr += ` and (${key} >= $${index} and ${key} <= $${index + 1}) `
              index += 2
            }
          } else if(typeof value === 'number') {
            param.push(value)
            sqlWhereStr += ` and ${key} = $${index} `
            index += 1
          } else if(typeof value === 'string') {
            param.push(`%${value}%`)
            sqlWhereStr += ` and ${key} like $${index} `
            index += 1
          }
        }
      }
    }
    param.push(pageSize)
    param.push(offset)
    let sqlStr = `SELECT * FROM public.${tableName} WHERE 1 = 1 ${sqlWhereStr} ORDER BY id DESC LIMIT $${index} OFFSET $${index + 1}`
    console.info('sql:',sqlStr,param)
    const dataRes = await pool.query(
      sqlStr,
      param
    );

    // 查询总记录数
    const countRes = await pool.query(`SELECT COUNT(*) FROM public.${tableName} WHERE 1 = 1 ${sqlWhereStr}`,param.slice(0, param.length - 2));
    const total = parseInt(countRes.rows[0].count);

    res.json({
      code: 200,
      message: '成功',
      data: {
        list: dataRes.rows,
        total,
      }
    });
  } catch (error) {
    console.error('查询失败:', error);
    res.status(500).json({
      code: 500,
      message: '服务器内部错误',
      data: null
    });
  }
});

/**
 * GET /save
 * 根据参数tableName通过数据库读取tableName对应库表的元数据信息
 */
app.post('/saveData', async (req, res) => {
  const { tableName,data } = req.body;
  
  try {
    if(data.id) {
      //修改
      let values = []
      let sqlStr = `UPDATE ${tableName} SET `
      let index = 1
      for (const key in data) {
        if (data.hasOwnProperty(key) && key != 'id') {
          const value = data[key];
          sqlStr += `${key} = $${index++},`
          values.push(value)
        }
      }
      sqlStr = sqlStr.slice(0, -1) + ` WHERE id = $${index} RETURNING *`
      values.push(data.id)
      console.info('sql:',sqlStr,values)
      const result = await pool.query({
        text: sqlStr,
        values: values,
      });
      if (result.rowCount === 0) {
        res.status(500).json({
          code: 500,
          message: `${tableName}未找到 ID 为 ${data.id} 的数据`,
          data: null
        });
      } else {
        res.json({
          code: 200,
          message: '成功',
          data: result.rows[0]
        });
      }
    }else {
      //新增
      let fields = ['id']
      let values = []
      let placeholder = ['$1']
      let index = 2
      //生成id
      values.push(uuidv4())
      for (const key in data) {
        if (data.hasOwnProperty(key)) {
          const value = data[key];
          fields.push(key)
          values.push(value)
          placeholder.push(`$${index++}`)
        }
      }
      let sqlStr = `INSERT INTO ${tableName} (${fields.join(',')}) VALUES(${placeholder.join(',')}) RETURNING id`
      console.info('sql:',sqlStr,values)
      const result = await pool.query({
        text: sqlStr,
        values: values,
      });

      res.json({
        code: 200,
        message: '成功',
        data: result
      });
    }
  } catch (error) {
    console.error('保存失败:', error);
    res.status(500).json({
      code: 500,
      message: '服务器内部错误',
      data: null
    });
  }
});

/**
 * POST /deleteByIds
 * 根据参数tableName及ids删除对应库表对应id的数据
 */
app.post('/deleteByIds', async (req, res) => {
  const { tableName,ids } = req.body; // express.json() 解析

  try {
    let sqlStr = `DELETE FROM public.${tableName} WHERE id IN (`
    let index = 1
    let placeholder = []
    ids.forEach(id => placeholder.push(`$${index++}`))
    sqlStr += `${placeholder.join(',')})`

    console.info('sql:',sqlStr,ids)
    const countRes = await pool.query(sqlStr,ids);
    const total = parseInt(countRes.rowCount);

    res.json({
      code: 200,
      message: '成功',
      data: total,
    });
  } catch (error) {
    console.error('删除失败:', error);
    res.status(500).json({
      code: 500,
      message: '服务器内部错误',
      data: null
    });
  }
});

// --- 错误处理中间件 (可选) ---
app.use((err, req, res, next) => {
  console.error(err.stack);
  res.status(500).json({
    code: 500,
    message: '服务器内部错误',
    data: null
  });
});

// --- 启动服务器 ---
app.listen(PORT, () => {
  console.log(`后端服务已启动，监听端口: http://localhost:${PORT}`);
});